SQL Functions

SQL functions are of two types:

Aggregate functions

These functions return a single value, calculated from the values in the column:

·         AVG() : Returns a average value in the column.
·         FIRST(): Returns the first value in the column.
·         SUM(): Returns the sum in the column.
·         MAX(): Returns the largest value in the column.
·         LAST(): Returns the last value in the column.
·         COUNT():Returns the number of rows.
·         MIN(): Returns the minimum value in the column.

Scalar functions

Scalar functions return a single value, based on input data. Following are the various types of scalar functions:

· UCASE(): Converts the column into upper case.

· NOW(): Returns the current date and time of the system.

· LEN(): Returns the length of the field.

· LCASE(): converts the column into lower case.

· MID(): Extract data from a text field.

· ROUND(): Rounds a numeric field to the number of decimal specified.

· FORMAT(): Formats how a column is to be displayed.

NOTE: First create a database of student name and then create a table. After creating table insert the values in the table. Following is an example which will be used by SQL functions:

Customer Id	Name	Address	Contact Marks
12ST09	John	USA	456788 54
12ST10	Royce	Argentina	457912 34
12ST11	Mickel	London	56276 45
12ST12	Tom	New york	2898938 43
12ST13	Hugh	Luanda	2974789 34
12ST14	Nolan	Paris	232688 56
12ST15	Jennifer	USA	43762762 67

UCASE()

UCASE () function takes a character as a parameter and returns a string in which all characters have been converted into uppercase.

SQL UCASE() syntax:

Select UCASE(Column name) from table name;

Please note: If the character type char then it will return char, otherwise it will return varchar. Following is an example of UCASE() function:

JOHN

ROYCE

MICKEL

TOM

HUGH

NOLAN

JENNIFER

LCASE()

LCASE () function takes a character as a parameter and returns a string in which all characters have been converted into lowercase.

SQL LCASE() syntax:

Select LCASE(Column name) from table name;

Please note: If the character type char then it will return char, otherwise it will return varchar. Following is an example of LCASE() function:

Name

john

royce

mickel

tom

hugh

nolan

jennifer

MID()

The MID() function is used to extract characters from text column. Following is a syntax of MID() function:

SELECT MID(column name, start, length) FROM table name;

Please note: column name and start is mandatory where as length can be option. If length parameter is omitted, then MID() function will return rest of the text.

Following is an example of MID() function:

John

Royc

Mick

Tom

Hugh

Nola

Note: If length parameter is omitted, then MID() function will return rest of the text.

LEN()

The LEN() function returns the length of the column field. Following is the syntax of LEN() function:

SELECT LEN(column name) FROM table name;

It will return the length of the column field.

ROUND()

The ROUND() is used to round the number decimal at which it is specified. Following is the syntax of ROUND()function:

SELECT ROUND(column name, decimals) FROM table name;

Note: column name and decimals parameter is important to give.

NOW()

The NOW() function returns the current system date and time. Following is the syntax of NOW() function:

SELECT NOW() FROM table name;

Following is an output for NOW() function:

2016-06-29 14:18:50



2016-06-29 14:18:50



2016-06-29 14:18:50



2016-06-29 14:18:50



2016-06-29 14:18:50



2016-06-29 14:18:50



2016-06-29 14:18:50

FORMAT()

FORMAT() function is used to format how function is to be displayed. Following is the syntax of NOW() function:

SELECT FORMAT(column name, format) FROM table name;

For example:

SELECT FORMAT(Now(),'YYYY-MM-DD') FROM student_ info;;

AVG()

AVG() function is used to return the average value of numeric function. Following is the syntax of AVG() function:

SELECT AVG(column name) FROM table name;

In our example, column name will be the Marks which will return the average of all 7 students:

45.50

COUNT()

Count() function is very useful in counting the number of records, which is returned by SELECT statement. Following is the syntax of COUNT() function:

SELECT COUNT(*) FROM table name;

Above function will count the number of records present in that table. Following is the example of COUNT() function:

SELECT COUNT(*) FROM student_info;

Output:

The number of rows present in student_info is 7.

MAX()

MAX() function is used to count the maximum value in the given record. Following is the syntax of MAX() function:

SELECT MAX(column name) FROM table name;

Above function will give the maximum value in the given column name. Following is the example of MAX() function:

SELECT MAX(marks) FROM student_info;

Output:

Above function had given the maximum marks of a student which is 67.

MIN()

MIN() function is used to count the minimum value in the given record. Following is the syntax of MIN() function:

SELECT MIN(column name) FROM table name;

Above function will give the minimum value in the given column name. Following is the example of MIN() function:

SELECT MIN(marks) FROM student_info;

Output:

Above function had given the minimum marks of a student which is 34.

LAST()

LAST() function is used to last value in the given record. Following is the syntax of LAST() function:

SELECT LAST(column name) FROM table name;

Above function will give the last name in the given column name. Following is the example of LAST() function:

SELECT LAST(name) FROM student_info;

Output:

Jennifer

Above function had given the name of column.

FIRST()

FIRST() function is used to first value in the given record. Following is the syntax of FIRST() function:

SELECT FIRST(column name) FROM table name;

Above function will give the first name in the given column name. Following is the example of FIRST()

function:

SELECT FIRST(name) FROM student_info;

Output:

John

Above function had given the name of column.

SUM()

SUM() function is used to give the sum of the column in the given record. Following is the syntax of SUM() function:

SELECT SUM(column name) FROM table name;

Above function will give the sum of the given column in the given table. Following is the example of SUM() function:


SELECT SUM(marks) FROM student_info;

Output:

333

Above function had given the sum of student marks.

User Defined Functions

To create a function CREATE FUNCTION statement is used. Following is the syntax is used to CREATE FUNCTION:

CREATE FUNCTION name_of_function(parameter1,parameter2,…)



    RETURNS datatype



   [NOT] DETERMINISTIC



Code block

In above syntax, CREATE FUNCTION is used to create a function followed by the name of the function.

Parameter1, parameter2 are the parameters of the function inside the parentheses. You cannot specify INOUT, IN or OUT modifiers to the parameters. By default, all parameters are IN parameters.

In RETURN statement you must specify the data type of the return value.

If stored function does not return the same result it is termed as non deterministic and if return same result than it is considered as a deterministic.

In the body of the stored function code is written. It can be compound statements or a single statement. Inside the body at least one return statement should be specify.

blog

SQL functions are of two types:

Aggregate functions

UCASE()

SQL UCASE() syntax:

ROUND()

For example:

AVG()

MAX()

FIRST()

SUM()

Samuel Fernandes

Leave Comment

1 Comments

Comments

Liked By